<?php
//-----------------------------------------------------------------
// CSVimporter PHP Class adapted from Matt Palermo by cero 2013
//-----------------------------------------------------------------
// This class converts a CSV file into records for a MySQL table.  
// It will import the data from a CSV file, optionally perform
// operations on each data field and then import it into a table
// specified during use.
//-----------------------------------------------------------------
//  http://sweetphp.com
//-----------------------------------------------------------------


class CSVimporter
{
	var $csvFile;					// Full path to csv file
	var $topLineFields = false;		// Is the first line of the CSV field names to use
	var $fieldSeparator = ";";		// Character that separates fields
	var $fieldEnclosure = "\"";		// The character that encloses each field
	var $toplineignore = true;		// The first line contains titles ans must be ignored	
	var $fieldNamesArray;			// Array to hold the field names of the file content
	var $rowWalker = "";			// The array_walk function to call for each row's data array
	var $fileHandler;				// File handler for csv
	var $showDebug = false;			// Show debugging information
	var $errorCSV = "";				// Return error from CSV file
	var $errorSQL = "";				// Return error from SQL query
    /**
     * Constructor
     *
     * @file		string			The filename (with full path) of the CSV file to import
     * @params		array			OPTIONAL: Extra parameters to send to the constructor.  Array example:
	 *									array(
	 *										topLineFields => false,			// The first line of the CSV file does not contain field names
	 *										fieldSeparator => ",",			// The CSV data fields are separated by a comma
	 *										fieldEnclosure => "\"",			// Double quotes enclose each field containing commas
	 *										rowWalker => "formatMyData"		/ The name of the function to call to format each CSV line before import.
	 *																		// This function can be used to convert date strings to timestamps or modify
	 *																		// any other data before import.  This is the name of a function that you supply.
	 *									)
	 *								Any or ALL of the above array values can be specified.  They will override the default values of the class
     *
	 * @return 		void			No value returned
     */
	function CSVimporter($file, $params=NULL)
	{
		if(!is_file($file)) die("The CSV file (".$file.") does not exist.");
		$this->csvFile = $file;
		
		// See if additional parameters were passed in
		if(is_array($params))
		{
			while(list($var, $val) = each($params))
			{
				if(isset($val)) $this->$var = $val;
			}
		}
	}
	
    /**
     * Set the field names for the csv data
     *
     * @fields		array			If the first line of your CSV file does not contain the field names of the data, you can use this method
	 *								to create them.  Heres an example of a CSV file with 7 fields that we want to specify names for:
	 *									array("Field 1", "Field 2", "Field 3", "Field 4", "Field 5", "Field 6", "Field 7")
	 *								The parameter is an array of string values that will be the field names of the table
	 *
     * @return 		void			No value returned
     */
	public function setFieldNames($fields)
	{
		$handle = fopen($this->csvFile, "r");       
		if ($this->toplineignore)	($firstline= fgetcsv($handle, 1000, $this->fieldSeparator, $this->fieldEnclosure));   //ignore 1st line of CVS file
				
		while(($data =& fgetcsv($handle, 1000, $this->fieldSeparator, $this->fieldEnclosure)) !== FALSE) 
		{
			$fieldCount = count($data);
			break;
		}
		fclose($handle);
		
		// Make sure number of field names match
		global $errorCSV;
		if(count($fields) != $fieldCount) 	{
											($errorCSV="There are ".$fieldCount." fields in the CSV file, you have supplied ".count($fields)." field names.");
											return $errorCSV;
											}
	 
		$this->fieldNamesArray = $fields;
	}
	
    /**
     * Gets and array of the csv contents.  This method is mostly used by the class and really isn't needed to be called.
     *
     * @return 		array			The array returned is a multidemensional array of the data in the csv file. 
							Each array key ([0], [1], etc.) represents a row of data from the csv file
     */
	function getCSVarray()
	{
		// Make sure the field names array is set first
		if(!$this->topLineFields && count($this->fieldNamesArray) < 1) {
																		($errorCSV="You must set the field names");
																		return $errorCSV;
																		}
		$line = 1;
		$dataArray = array();
		$this->fileHandler = fopen($this->csvFile, "r");
		if ($this->toplineignore) ($firstline= fgetcsv($this->fileHandler, 1000, $this->fieldSeparator, $this->fieldEnclosure));   //ignore 1st line of CVS file
		while(($data =& fgetcsv($this->fileHandler, 1000, $this->fieldSeparator, $this->fieldEnclosure)) !== FALSE) 
		{
			// Set first line as field names
			if($line == 1 && $this->topLineFields) $this->fieldNamesArray = $data;
			else
			{
				$info = array();
				for($i = 0; $i < count($data); $i++)
				{
					$fieldArray = $this->fieldNamesArray;
					$info[$fieldArray[$i]] = $this->formatFieldData($data[$i]);
				}
				
				// If there is a user defined array walk to format row data, do it here
				if(!empty($this->rowWalker)) array_walk($info, $this->rowWalker);
				
				$dataArray[] = $info;
				
				if(count($data) != count($this->fieldNamesArray))
				{
					array_pop($dataArray);
					if($this->showDebug) echo "The following line was not included, because the number of fields did not match:<br /><b>".implode($this->fieldSeparator, $data)."</b><br /><br />";
				}
			}
			$line++;
		}
		//echo "<pre>"; print_r($dataArray); echo "</pre>";
		fclose($this->fileHandler);
		return $dataArray;
	}
	
    /**
     * Get array of sql insert statements to put the data into a table
     *
     * @sqlTable	string			String representing the name of the MySQL table in which to place the csv data.  The table must have fields
	 *								with the same field names as the imported data.
	 *
     * @return 		array			Array of sql queries in string form to insert the csv data
     */
	function getSQLinsertsArray($sqlTable)
	{
		$data =& $this->getCSVarray();
		$queries = array();
		$fieldsStr = "";
		while(list($k, $field) = each($data))
		{
			if(empty($fieldStr)) $fieldStr = implode(", ", array_keys($field));
			
			array_walk ($field, array($this, "sqlPrepare"));
			$valueStr = "'".implode("', '", $field)."'";

			$queries[] = "INSERT INTO ".$sqlTable." (".$fieldStr.") VALUES (".$valueStr.");";
			//echo $query."<br />";
		}
		return $queries;
	}
	
    /**
     * Run the sql queries to import info into db
     *
     * @sqlTable	string			String representing the name of the MySQL table in which to place the csv data.  The table must have fields
	 *								with the same field names as the imported data.
	 *
     * @return 		void			No value returned
     */
	public function queryInto($sqlTable)
	{	
		global $errorSQL;
		$queries =& $this->getSQLinsertsArray($sqlTable);
		while(list($k, $query) = each($queries))
		{
			mysql_query($query) or ($errorSQL=mysql_error());
			if(!$errorSQL =="") break;
		}
				return $errorSQL ; 
	}
	
	
    /**
     * Prepare array data for SQL inserts
     *
     * @val			string			CSV field data that needs to be formatted before being inserted into the table
	 * @key			string			CSV field string for the array index
	 *
     * @return 		void			No value returned
     */
	function sqlPrepare(&$val, $key) 
	{
		$val = addslashes($val);
	}

	
	/**
     * Format the field to clean the data and converts to UTF8
     *
     * @field		string			CSV field data that needs to be cleaned
	 *
     * @return 		void			No value returned
     */
	function formatFieldData($field)
	{
		$field = str_replace("\\".$this->fieldEnclosure, $this->fieldEnclosure, $field);  // Remove escape chars
		$field = utf8_encode($field) ;   // converts to UTF8
		return $field;
	}	
}

?>